package com.learning.platform.repository;

import com.learning.platform.entity.LearningSession;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 学习会话数据访问接口
 */
@Repository
public interface LearningSessionRepository extends JpaRepository<LearningSession, Long> {
    
    /**
     * 查找用户未结束的学习会话
     */
    List<LearningSession> findByUserIdAndEndTimeIsNullOrderByStartTimeDesc(Long userId);
    
    /**
     * 查找用户在特定课程的学习会话
     */
    List<LearningSession> findByUserIdAndCourseIdOrderByStartTimeDesc(Long userId, Long courseId);
    
    /**
     * 查找用户在特定章节的学习会话
     */
    List<LearningSession> findByUserIdAndLessonIdOrderByStartTimeDesc(Long userId, Long lessonId);
    
    /**
     * 查找用户在指定日期的学习会话
     */
    @Query("SELECT ls FROM LearningSession ls WHERE ls.userId = :userId AND " +
           "DATE(ls.startTime) = :date ORDER BY ls.startTime ASC")
    List<LearningSession> findByUserIdAndDate(@Param("userId") Long userId, @Param("date") LocalDate date);
    
    /**
     * 统计用户在指定时间范围内的总学习时长
     */
    @Query("SELECT COALESCE(SUM(ls.durationSeconds), 0) FROM LearningSession ls WHERE ls.userId = :userId AND " +
           "ls.startTime BETWEEN :startTime AND :endTime AND ls.endTime IS NOT NULL")
    Long sumStudyTimeByUserIdAndTimeBetween(@Param("userId") Long userId,
                                           @Param("startTime") LocalDateTime startTime,
                                           @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计用户每日学习时长（最近N天）
     */
    @Query("SELECT DATE(ls.startTime) as studyDate, COALESCE(SUM(ls.durationSeconds), 0) as totalSeconds " +
           "FROM LearningSession ls WHERE ls.userId = :userId AND " +
           "ls.startTime >= :startDate AND ls.endTime IS NOT NULL " +
           "GROUP BY DATE(ls.startTime) ORDER BY studyDate ASC")
    List<Object[]> getDailyStudyTime(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate);
    
    /**
     * 统计用户课程学习时长排行
     */
    @Query("SELECT ls.courseId, COALESCE(SUM(ls.durationSeconds), 0) as totalSeconds " +
           "FROM LearningSession ls WHERE ls.userId = :userId AND ls.endTime IS NOT NULL " +
           "GROUP BY ls.courseId ORDER BY totalSeconds DESC")
    List<Object[]> getCourseStudyTimeRanking(@Param("userId") Long userId);
    
    /**
     * 统计用户学习类型分布
     */
    @Query("SELECT ls.sessionType, COUNT(ls), COALESCE(SUM(ls.durationSeconds), 0) " +
           "FROM LearningSession ls WHERE ls.userId = :userId AND ls.endTime IS NOT NULL " +
           "GROUP BY ls.sessionType")
    List<Object[]> getSessionTypeStats(@Param("userId") Long userId);
    
    /**
     * 获取用户最近的学习会话
     */
    Optional<LearningSession> findFirstByUserIdOrderByStartTimeDesc(Long userId);
    
    /**
     * 统计用户连续学习天数
     */
    @Query(value = "SELECT COUNT(DISTINCT DATE(start_time)) FROM learning_sessions " +
                  "WHERE user_id = :userId AND end_time IS NOT NULL AND " +
                  "DATE(start_time) >= CURDATE() - INTERVAL :days DAY", nativeQuery = true)
    long countStudyDaysInLastNDays(@Param("userId") Long userId, @Param("days") int days);
    
    /**
     * 获取用户学习热力图数据（按小时统计）
     */
    @Query("SELECT HOUR(ls.startTime) as studyHour, COUNT(ls) as sessionCount, " +
           "COALESCE(SUM(ls.durationSeconds), 0) as totalSeconds " +
           "FROM LearningSession ls WHERE ls.userId = :userId AND ls.endTime IS NOT NULL " +
           "GROUP BY HOUR(ls.startTime) ORDER BY studyHour ASC")
    List<Object[]> getStudyHeatmapData(@Param("userId") Long userId);
    
    /**
     * 删除课程的所有学习会话
     */
    void deleteByCourseId(Long courseId);
    
    /**
     * 删除章节的所有学习会话
     */
    void deleteByLessonId(Long lessonId);
}


